/* $RCSFile: DatabaseDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/ADNCRM.Core/DAL/DatabaseDB.cs $
 * $log$
 * Revision 19 2010/08/16 22:52:09  christian.surieux
 *   Suppress telerik version from web.config, Add comments for connection string, Add dbo. to adn_version table creation source in DBDataStore
 * Revision 6 2010/08/06 19:56:28  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Globalization;

using AddonNice.Configuration;
using AddonNice.Helpers;
using AddonNice.Diagnostics;

namespace AddonNice.Settings
{
	public class DatabaseDB : DBDataStore
	{

        /// <summary>
        /// CS 23/1/6 for Setup, clearing Db version forces a new read
        /// </summary>
        public static void ClearDatabaseVersion()
        {
            HttpContext.Current.Application.Lock();
            HttpContext.Current.Application[Config.dbVersionKey] = null;
            HttpContext.Current.Application.UnLock();
        }

        /// <summary>
        /// DatabaseDB version should be equal to running code version release (CodeVersion)
        /// Version is extracted from the adn_version table, which also contains license information (TBD)
        /// 1) adn_version is created if missing.
        /// This is expecially for empty databases.
        /// 2) Connection problems are thown immediately as errors.
        /// </summary>
        public static int DatabaseVersion
        {
            get
            {
                //Caches dbversion
                int curVersion          =   0;
                object ob               =   HttpContext.Current.Application[Config.dbVersionKey];
                if ( ob != null)
                    return (int)ob;
                //Create rbversion if it is missing
                string createDbVersions =
                    "IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[adn_Versions]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)" +
                    " CREATE TABLE  [dbo].[adn_Versions] (" +
                    "[Release] [int] NOT NULL , " +
                    "[Version] [nvarchar] (50) NULL , " +
                    "[ReleaseDate] [datetime] NULL " +
                    ") ON [PRIMARY]";
                int returnValue         =   -1;
                SqlConnection cnx       =   CreateStaticConnection();
                SqlCommand sqlCommand   =   new SqlCommand(createDbVersions, cnx);
                try
                {
                    sqlCommand.Connection.Open();
                    returnValue         =   sqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    string msg          =   string.Format("[E]DatabaseDB DatabaseVersion If this fails most likely cannot connect to db or no permission,  ex : {0}", ex);
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( msg,DBTraceSwitch.Sw.Info);
                    if ( cnx.State == System.Data.ConnectionState.Open )
                        cnx.Close();
                    //If this fails most likely cannot connect to db or no permission
                    throw new DatabaseUnreachableException(msg, ex);
                }
                object version	        =	null;
                string cmd		        =	"SELECT TOP 1 Release FROM adn_Versions ORDER BY Release DESC";
                sqlCommand		        =	new SqlCommand(cmd,cnx);
                try
                {
                    version             =   sqlCommand.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    string msg          =   string.Format("[E]DatabaseDB DatabaseVersion If this fails most likely cannot connect to db or no permission,  ex : {0}", ex);
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( msg,DBTraceSwitch.Sw.Info);
                    //If this fails most likely cannot connect to db or no permission
                    throw new DatabaseUnreachableException(msg, ex); 
                }
                finally
                {
                    if (cnx.State == System.Data.ConnectionState.Open)
                        cnx.Close();
                }

                if (  version != null )
                        curVersion	= Int32.Parse(version.ToString());
                else    curVersion	= -1; // no db we need a full install
                // save it in Application for every request
                HttpContext.Current.Application.Lock();
                HttpContext.Current.Application[Config.dbVersionKey] = curVersion;
                HttpContext.Current.Application.UnLock();
                // CS 6/3/4
                return curVersion;
            }
        }

        #region Get All history

		/// <summary>
		/// Get all the pages in this portal ordered by asc parentTabID and order.
        /// If current portal is admin returns all pages, else return only public pages from adn_sitemap
		/// </summary>
		public SqlDataReader GetAllDbVersion()
		{
			SqlCommand myCommand	    =   new SqlCommand("dbo.adn_GetAllDbVersions", myConnection);
			myCommand.CommandType		=   CommandType.StoredProcedure;
            SqlDataReader result		=   null;
            try
            {
                myConnection.Open();
                result					=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]DatabaseDB GetAllDbVersion ex: {0}", ex),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
        }
        
        #endregion Get All history

        #region Packages
        /// <summary>
		/// Get last Packages release/version with PackageName from table adn_packages
		/// </summary>
		public SqlDataReader GetCurrentPackage(string packageName)
		{
			SqlCommand myCommand	            =   new SqlCommand("dbo.adn_GetCurrentPackage", myConnection);
			myCommand.CommandType		        =   CommandType.StoredProcedure;
            SqlDataReader result		        =   null;
            SqlParameter parameterPackageName	=   new SqlParameter(StStr.ATPackageName, SqlDbType.NVarChar, 50);
            parameterPackageName.Value	        =   packageName;
            myCommand.Parameters.Add(parameterPackageName);

            try
            {
                myConnection.Open();
                result					=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]DatabaseDB GetCurrentPackage ex: {0}", ex),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
        }
        
		/// <summary>
		/// Get all the Packages releases and versions with PackageName from table adn_packages
		/// </summary>
		public SqlDataReader GetPackage(string packageName)
		{
			SqlCommand myCommand	            =   new SqlCommand("dbo.adn_GetPackage", myConnection);
			myCommand.CommandType		        =   CommandType.StoredProcedure;
            SqlDataReader result		        =   null;
            SqlParameter parameterPackageName	=   new SqlParameter(StStr.ATPackageName, SqlDbType.NVarChar, 50);
            parameterPackageName.Value	        =   packageName;
            myCommand.Parameters.Add(parameterPackageName);

            try
            {
                myConnection.Open();
                result					=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]DatabaseDB GetPackage ex: {0}", ex),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
        }
        
		/// <summary>
		/// Get all the Packages releases and versions with PackageName from table adn_packages
		/// </summary>
		public SqlDataReader GetAllPackages()
		{
			SqlCommand myCommand	            =   new SqlCommand("dbo.adn_GetAllPackages", myConnection);
			myCommand.CommandType		        =   CommandType.StoredProcedure;
            SqlDataReader result		        =   null;

            try
            {
                myConnection.Open();
                result					=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]DatabaseDB GetAllPackages ex: {0}", ex),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
        }

		/// <summary>
		/// DataTable version for GetAllPackages, Get all the Packages releases and versions with PackageName from table adn_packages
		/// </summary>
		public DataTable GetAllPackagesTb()
		{
			SqlCommand myCommand	            =   new SqlCommand("dbo.adn_GetAllPackages", myConnection);
			myCommand.CommandType		        =   CommandType.StoredProcedure;
            SqlDataAdapter myDa                 =   new SqlDataAdapter(myCommand);
            DataTable result		            =   new DataTable();

            try
            {
                myConnection.Open();
                myDa.Fill(result);
            }
            catch (Exception ex)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]DatabaseDB GetAllPackages ex: {0}", ex),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
            return result;
        }

        /// <summary>
        /// Insert a new package in DB
        /// </summary>
        /// <param name="packageName"></param>
        /// <param name="Release"></param>
        /// <param name="pVersion"></param>
        /// <param name="ReleaseDate"></param>
        public void AddPackage(string packageName,int Release,string pVersion,DateTime ReleaseDate)
        {
            SqlConnection theConnection         =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection		            =   newConnection;
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_AddPackage", theConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterPackageName	=   new SqlParameter(StStr.ATPackageName, SqlDbType.NVarChar, 50);
            //Fixes tab name to long
            if (packageName.Length > 50) 
					parameterPackageName.Value	=   packageName.Substring(0, 49);
            else	parameterPackageName.Value	=   packageName;
            myCommand.Parameters.Add(parameterPackageName);

            SqlParameter parameterRelease	    =   new SqlParameter(StStr.ATRelease, SqlDbType.Int, 4);
            parameterRelease.Value			    =   Release;
            myCommand.Parameters.Add(parameterRelease);

            SqlParameter parameterVersion	    =   new SqlParameter(StStr.ATVersion, SqlDbType.NVarChar, 50);
            parameterVersion.Value	            =   pVersion;
            myCommand.Parameters.Add(parameterVersion);

            SqlParameter parameterReleaseDate   =   new SqlParameter(StStr.ATReleaseDate, SqlDbType.DateTime);
            parameterReleaseDate.Value	        =   ReleaseDate;
            myCommand.Parameters.Add(parameterReleaseDate);

            try
            {
                theConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]DatabaseDB AddPackage ex: {0}",exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if ( theConnection.State == System.Data.ConnectionState.Open )
                    theConnection.Close();
            }
        }

       #endregion Packages
	}
}